

version 14
capture log close
set more off
        
		cd "${outdir}"
		
	    log using ilo_prep, replace t 

* ------------------------------------------------------------------------------
* Date : July 2017


* Project : Mena Economic Monitor, Fall 2016 edition (MNACE, The World Bank)


* This do file prepares the ILO database for merge

* Database used : ilo_raw.dta


* Output : ilo.dta

* ------------------------------------------------------------------------------


*import excel "${rootdir}\Data\Raw data\ILOSTAT\ILOSTAT_modif.xls", clear firstrow
*save "${rootdir}\Data\Raw data\ILOSTAT\ilo_raw.dta", replace
		
	
		
		use "${datadir}/ilo_raw.dta", clear
		drop Source Seriesfootnotes
		drop if Sex=="Female (Sex)" | Education=="Level not stated (Aggregate levels)"  | Education=="Less than basic (Aggregate levels)"
	
	*Reshape
		g delim="-"
		g _panel=Country+delim+Sex+delim+Education+delim+Age
		sort _panel
		drop in 1/5
		sort _panel 
		g       dup=1 if _panel[_n-1]!=_panel & _panel[_n+1]==_panel
		replace dup=2 if _panel[_n-1]==_panel & _panel[_n+1]==_panel
		replace dup=0 if _panel[_n-1]==_panel & _panel[_n+1]!=_panel
		tostring dup, replace
		g panel=_panel+delim+dup
		reshape long y, i(panel) j(year)
		sort year dup _panel
		gen acat=1 if Age=="15-64 (Age)"
			replace acat=2 if Age=="15+ (Age)"
			replace acat=3 if Age=="15-24 (Age)"
			replace acat=4 if Age=="25+ (Age)"
	
	*Loop over data waves
		destring dup, replace
		replace dup=0 if dup==.
		replace dup=dup+1
		save temp0.dta, replace
		forvalues j=1(1)3{
		use temp0.dta, clear
		keep if dup==`j'
	
	*Generate new unemployment vars
		g ilo`j'_unemp            =y if Education=="Total (Aggregate levels)" & Sex=="Total (Sex)"
		g ilo`j'_unemp_prim       =y if Education=="Basic (Aggregate levels)" & Sex=="Total (Sex)" 
		g ilo`j'_unemp_sec        =y if Education=="Intermediate (Aggregate levels)" & Sex=="Total (Sex)" 
		g ilo`j'_unemp_tert       =y if Education=="Advanced (Aggregate levels)" & Sex=="Total (Sex)" 	
		g ilo`j'_unemp_male       =y if Education=="Total (Aggregate levels)" & Sex=="Male (Sex)"
		g ilo`j'_unemp_male_prim  =y if Education=="Basic (Aggregate levels)" & Sex=="Male (Sex)" 
		g ilo`j'_unemp_male_sec   =y if Education=="Intermediate (Aggregate levels)" & Sex=="Male (Sex)" 
		g ilo`j'_unemp_male_tert  =y if Education=="Advanced (Aggregate levels)" & Sex=="Male (Sex)" 
				
	*Eliminate useless age categories, but replace missings with 2nd best
		forvalues i=2(1)4 {
		preserve
		keep if acat==`i'
		foreach v of varlist ilo* {
		ren `v' _acat`i'`v'
		}
		sort year Country Sex Education dup
		save temp_acat`i'.dta, replace
		restore
		}
		keep if acat==1
		forvalues i=2(1)4 {
		sort year Country Sex Education dup
		merge 1:1 year Country Sex Education dup using temp_acat`i'.dta , nogen
		*tab _merge
		*drop _merge
		foreach v of varlist ilo* {
		replace `v'=_acat`i'`v' if `v'==.
		}	
		drop _acat*
		}
	*Collapse to country-year panel
		collapse (min) ilo*, by(year Country)		
		sort year Country
		save temp_dup`j'.dta, replace
		}

		use temp_dup1.dta, clear
		merge 1:1 year Country using temp_dup2, nogen
		merge 1:1 year Country using temp_dup3, nogen
		
		foreach data in temp_acat2 temp_acat3 temp_acat4 temp_dup1 temp_dup2 temp_dup3 temp0 {
		cap erase `data'.dta
		}
		
		foreach v in _unemp _unemp_prim _unemp_sec _unemp_tert _unemp_male _unemp_male_prim _unemp_male_sec _unemp_male_tert {
		replace ilo1`v'=ilo2`v' if ilo1`v'==.
		replace ilo1`v'=ilo3`v' if ilo1`v'==.
		}
	
		ren Country countryname
		
	*Rename countries to merge	
		replace countryname="" if country==""
		replace countryname="" if country==""
		replace countryname="" if country==""
		replace countryname="South Korea" if country=="Korea, Republic of"
		replace countryname="Egypt, Arab Rep." if country=="Egypt"
		replace countryname="Gambia, The" if country=="Gambia"
		replace countryname="Moldova" if country=="Moldova, Republic of"
		replace countryname="Hong Kong SAR, China" if country=="Hong Kong, China"
		replace countryname="Kyrgyz Republic" if country=="Kyrgyzstan"
		replace countryname="Lao PDR" if country=="Lao People's Democratic Republic"
		replace countryname="Macedonia, FYR" if country=="Macedonia, the former Yugoslav Republic of"
		replace countryname="Palestine" if country=="Occupied Palestinian Territory"
		replace countryname="Slovak Republic" if country=="Slovakia"
		replace countryname="Tanzania" if country=="Tanzania, United Republic of"
		replace countryname="Venezuela, RB" if country=="Venezuela, Bolivarian Republic of"
		replace countryname="Yemen, Rep." if country=="Yemen"
		replace countryname="Vietnam" if country=="Viet Nam"
		
		drop if countryname=="San Marino" | countryname=="Samoa" |  countryname=="Réunion" ///
		| countryname=="Netherlands Antilles" | countryname=="Maldives" | countryname=="Macau, China"  ///
		| countryname=="Bermuda" | countryname=="Brunei Darussalam" | countryname=="Cayman Islands"  ///
		| countryname=="Falkland Islands (Malvinas)" | countryname=="French Polynesia" | year==2016
	             
		sort countryname year
		
	save "${outdir}/ilo.dta", replace 
		
		log close
		

